package com.szsh.aiot.hsm.mapper;

import com.szsh.aiot.hsm.dto.area.IArea;
import com.szsh.aiot.hsm.dto.area.IAreaSpe;
import org.apache.ibatis.annotations.*;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * I_AREA属地表数据操作
 *
 * @author zhangtt5
 *
 */
@Repository
public interface IAreaMapper {

    /**
     * 根据管理员属地查询下级属地
     *
     * @param areano
     * @return
     */
    @Select({ "<script>",
        "select * from I_AREA",
        "<where>",
            "<if test ='areano != null and areano !=\"\" and areano !=\"00\"'>",
            "areaNo=#{areano}",
            "</if>",
        "</where>",
    "</script>" })
    List<IArea> getArea(String areano);

    @Cacheable("getAllAreas")
    @Select("select * from I_AREA order by serno")
    List<IArea> getAllAreas();

    @Select("select * from I_AREA order by serno desc")
    List<IArea> getAllAreasdesc();

    @Select({ "<script>",
        "select * FROM I_AREA",
        "<where>",
            "<if test ='name != null and name !=\"\"'>",
                " and areaName like concat('%',concat(#{name},'%')) ",
            "</if>",
            "<if test ='powerlevel != 99'>",
                " and powerlevel=#{powerlevel} ",
            "</if>",
        "</where> ",
        "limit #{page}, #{limit} ",
    "</script>" })
    List<IArea> getPagination(@Param("name") String name, @Param("powerlevel") int powerlevel,
            @Param("page") int page, @Param("limit") int limit);

    @Select({ "<script>",
        "select count(*) FROM I_AREA",
        "<where>",
             "<if test ='name != null and name !=\"\"'> ",
                 "and areaName like concat('%',concat(#{name},'%')) ",
             "</if>",
            "<if test ='powerlevel != 99'>",
                 " and powerlevel=#{powerlevel} ",
             "</if>",
         "</where>",
    "</script>" })
    int getAreaCount(@Param("name") String name, @Param("powerlevel") int powerlevel);

    @Select("select * from I_AREA where areaNo=#{areano}")
    IArea getAreaByareano(@Param("areano") String areano);

    // 根据区号查询属地（市级）
    @Select("select areaNo from I_AREA where areaCode=#{areacode} and powerlevel=1 ")
    String getAreaByareacode(@Param("areacode") String areacode);

    @Select("select * from I_AREA where powerlevel=#{powerlevel}")
    List<IArea> getCityData(@Param("powerlevel") Integer powerlevel);

    @Insert({ "insert into I_AREA (areaNo, areaName, superarea, powerlevel,areaCode,regionId,channelId)",
            "values (#{areaNo}, #{areaName},#{superarea}, #{powerlevel},#{areaCode}, #{regionId},#{channelId})" })
    int insert(IArea area);

    @Delete("delete from I_AREA where areaNo = #{areano}")
    int delArea(String areano);

    @Update({ "update I_AREA set areaName=#{areaName},areaCode=#{areaCode},"
            + "regionId=#{regionId},channelId=#{channelId} where areaNo=#{areaNo}" })
    int update(IArea area);

    @Select("select areaNo,areaName from I_AREA where powerlevel=#{powerlevel}")
    @MapKey("areaNo")
    Map<String, Map<String, String>> getAreaMaps(int powerlevel);

    @Select({ "select areaNo, areaName, areaCode,regionId, channelId ", "from I_AREA",
            "where areaCode = #{areaCode}" })
    IArea select(@Param("areaCode") String areaCode);

    @Select({ "select areaNo, areaName, areaCode,regionId, channelId ", "from I_AREA",
            "where areaNo = #{areaNo}" })
    IArea selectByNo(@Param("areaNo") String areaNo);

    @Select("select * from I_AREA where powerlevel=#{powerlevel} order by areaNo")
    List<IArea> getAreaByPowerlevel(@Param("powerlevel") int powerlevel);

    /**
     * 根据上级属地和行政级别查询地区信息
     *
     * @param powerlevel
     * @param superarea
     * @return
     */
    @Select("select * from I_AREA where powerlevel=#{powerlevel} and superarea=#{superarea} order by areaNo")
    List<IArea> getAreaBySuper(@Param("powerlevel") Integer powerlevel,
            @Param("superarea") String superarea);

    @Cacheable(value = "getAreaByareaName", key = "'getArea_' + #p0 + '_' + #p1", unless = "#result == null")
    @Select({"<script>",
            "SELECT * FROM I_AREA WHERE areaName = #{areaName}",
            "and powerlevel=#{powerlevel}",
            "</script>"})
    IArea getAreaByareaName(@Param("areaName") String areaName,@Param("powerlevel") Integer powerlevel);

    @Select("SELECT * FROM I_AREA WHERE areaName = #{areaName}")
    IArea getAreaByName(String areaName);

    @Select("SELECT * FROM I_AREA WHERE areaName like #{areaName} order by powerlevel asc limit 1")
    IArea vaguegetAreaByName(String areaName);

    @Select("SELECT * FROM I_AREA_SPE WHERE speName = #{speName} and powerlevel=#{powerlevel}")
    IAreaSpe getAreaSpeByAreaName(@Param("speName") String speName, @Param("powerlevel") Integer powerlevel);
}
